package me.chyxion.dao.mybatis.pagination;

import java.sql.PreparedStatement;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import me.chyxion.dao.mybatis.pagination.PageParam.Sort;
import me.chyxion.spring.ext.utils.WordUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @version 0.0.1
 * @author Shaun Chyxion <br />
 * chyxion@163.com <br />
 * Jul 4, 2014 8:58:54 PM
 */
public abstract class DbDialect {
	private static final Logger log = LoggerFactory.getLogger(DbDialect.class);

	//protected static final String SQL_EOL = ";";
	protected static final Pattern PATTERN_FROM = 
		Pattern.compile("(?i)\\s+from\\s+");
	protected static final Pattern PATTERN_ORDER_BY = 
		Pattern.compile("(?i)\\s+order\\s+by\\s+");
	protected static final Pattern PATTERN_GROUP_BY = 
		Pattern.compile("(?i)\\s+group\\s+by\\s+");

	private static final Pattern JDBC_URL_PATTERN = 
		Pattern.compile("(?i)^jdbc:([^:]+):");
	
	/**
	 * select count(1) from users
	 * @param strSQL
	 * @return
	 */
	public String getCountSQL(String strSQL) {
		StringBuilder sbSQL = new StringBuilder(strSQL);
		// remove order by clause
		int index = indexOderBy(sbSQL);
		if (index > 0) {
			sbSQL.setLength(index + 1);
		}
		// delete to from
		sbSQL.delete(0, indexOfFrom(sbSQL));
		
		if (indexOfGroupBy(sbSQL) > 0) {
			sbSQL.insert(0, "select count(1) from (select 1 ");
			sbSQL.append(") t");
		}
		else {
			sbSQL.insert(0, "select count(1) ");
		}
		log.debug("Generate Count SQL: [{}].", sbSQL);
		return sbSQL.toString();
	}

	/**
	 * select name from users limit 10, 20
	 * @param targetSQL
	 * @param pageParam
	 * @return
	 */
    public abstract String getSimplePageSQL(final String targetSQL, final PageParam<?> pageParam);

	/**
	 * select name from users limit ?, ?
	 * @param targetSQL
	 * @param pageParam
	 * @return
	 */
    public abstract String getPreparedPageSQL(final String targetSQL, final PageParam<?> pageParam);

    /**
     * before query, you can set prepared statement extra values here. <br />
	 * ps.setInt(paramsSize + 1, pageParam.getStart()); <br />
	 * ps.setInt(paramsSize + 2, pageParam.getLimit());
     * @param ps
     * @param pageParam
     * @param originalArgsSize
     */
	public void beforeQuery(PreparedStatement ps, 
			PageParam<?> pageParam, 
			int originalArgsSize) throws Exception {
		ps.setInt(originalArgsSize + 1, pageParam.getStart());
		ps.setInt(originalArgsSize + 2, pageParam.getLimit());
	}

    /**
     * get Database Type By URL
     * @param jdbcUrl jdbc:mysql://localhost:3306/db_demo
     * @return mysql
     */
    public static String getDbType(String jdbcUrl) {
    	log.debug("Get Database Type By URL [{}].", jdbcUrl);
    	String type = null;
        if (jdbcUrl != null) {
			Matcher m = JDBC_URL_PATTERN.matcher(jdbcUrl);
			if (m.find()) {
				type = m.group(1).toLowerCase();
			}
        }
    	log.debug("Database Type [{}] Found.", type);
        return type;
    }

    /**
     * wrap word
     * @param name
     * @return
     */
	private String wrapWord(String name) {
		return leftWordWrapper() + name + rightWordWrapper();
	}

	/**
	 * build order by SQL
	 * @param pageParam
	 * @return
	 */
	protected String buildOrderBy(PageParam<?> pageParam) {
		List<String> orderBy = new LinkedList<String>();
		List<Sort> sorts = pageParam.getSorts();
		if (sorts != null && sorts.size() > 0) {
			for (Sort sort : pageParam.getSorts()) {
				orderBy.add(new StringBuilder()
					.append(wrapWord(
						WordUtils.joinCamelCase(sort.getColumn(), "_")
							.toLowerCase()))
					.append(" ")
					.append(sort.getDirection())
					.toString());
			}
		}
		// default order by date_created
		else {
			orderBy.add(wrapWord("date_created"));
		}
		return StringUtils.join(orderBy, ", ");
	}

    /**
     * database variable left wrapper, for example: 
     * MySQL: `
     * Oracle: "
     * SQLServer: [
     * @return
     */
	public String leftWordWrapper() {
		return "";
	}

	/**
     * database variable right wrapper, for example: 
     * MySQL: `
     * Oracle: "
     * SQLServer: ]
	 * @return
	 */
	public String rightWordWrapper() {
		return "";
	}

	/**
	 * index of [from] in SQL
	 * @param sql
	 * @return
	 */
	public int indexOfFrom(CharSequence sql) {
		return indexOf(PATTERN_FROM, sql);
	}

	/**
	 * index of [group by] in SQL
	 * @param sql
	 * @return
	 */
	public int indexOfGroupBy(CharSequence sql) {
		return indexOf(PATTERN_GROUP_BY, sql);
	}

	/**
	 * index of [order by] in SQL
	 * @param sql
	 * @return
	 */
	public int indexOderBy(CharSequence sql) {
		return indexOf(PATTERN_ORDER_BY, sql);
	}

	/**
	 * index of pattern
	 * @param pattern
	 * @param sql
	 * @return
	 */
	private int indexOf(Pattern pattern, CharSequence sql) {
		Matcher m = pattern.matcher(sql);
		return m.find() ? m.start() : -1;
	}
}